package com.pioneer.dao;

import com.pioneer.beans.Manifest;
import com.pioneer.beans.Container;
import com.pioneer.beans.ContainerContent;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;


/**
 * Title:        Pioneer Application
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:      Pioneer Metals
 * @author Gary Chen
 * @version 1.0
 */

public class JdbcManifestDAO
	extends JdbcDAO
	implements ManifestDAO {

	
    public JdbcManifestDAO(Properties properties)
	throws DataAccessException
	{
		super(properties);
    }

	public List getManifests()
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List manifests=new ArrayList();
		try
		{
			
			stat=connection.createStatement();
			
			rs=stat.executeQuery("select * from manifest order by job_num desc");
			while (rs.next())
			{
				manifests.add(new Manifest(rs.getString("job_num"),
					rs.getString("manifest_type"),
				rs.getString("vessel"),rs.getDate("etd"),
				rs.getDate("eta"),rs.getString("container_notes"),
				rs.getFloat("freight"),rs.getFloat("trucking"),rs.getFloat("misc")));
			}
		
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		/*Manifest[] manifestArr=new Manifest[manifests.size()];
		for (int count=0;count>manifestArr.length;count++)
		{
			manifestArr[count]=(Manifest)manifests.get(count);
		}
		return manifestArr;*/
		return manifests;
    }
    public Manifest getManifest(String jobNum)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Manifest manifest=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from manifest where "+
			"job_num='"+jobNum+"'");
			if (rs.next())
			{
				manifest=new Manifest(rs.getString("job_num"),rs.getString("manifest_type"),
					rs.getString("vessel"),rs.getDate("etd"),
				rs.getDate("eta"),rs.getString("container_notes"), rs.getFloat("freight"),
					rs.getFloat("trucking"),rs.getFloat("misc"));
				List containerList=getContainersWithContent(jobNum);
				for (int count=0;count<containerList.size();count++) {
					Container container=(Container)containerList.get(count);
					manifest.addContainer(container);
				}
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return manifest;
    }
    public void addManifest(Manifest manifest)
	throws DataAccessException {
		java.sql.Date sqlEtaDate=new java.sql.Date(manifest.getEta().getTime());
		java.sql.Date sqlEtdDate=new java.sql.Date(manifest.getEtd().getTime());
		String sqlString= "INSERT INTO manifest (job_num,manifest_type,vessel,etd,eta,"+
		"container_notes) VALUES ('"+sqlSafe(manifest.getJobNum())+"','"+
		sqlSafe(manifest.getManifestType())+"','"+
		sqlSafe(manifest.getVessel())+"','"+sqlEtdDate+"','"+sqlEtaDate+"','"+
		sqlSafe(manifest.getContainerNotes())+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateManifest(String jobNum, Manifest manifest)
	throws DataAccessException {
		java.sql.Date sqlEtaDate=new java.sql.Date(manifest.getEta().getTime());
		java.sql.Date sqlEtdDate=new java.sql.Date(manifest.getEtd().getTime());
		String sqlString="UPDATE manifest set manifest_type='"+sqlSafe(manifest.getManifestType())+
			"', vessel='"+sqlSafe(manifest.getVessel())+
		"',etd='"+sqlEtdDate+"',eta='"+sqlEtaDate+"',"+
		"container_notes='"+sqlSafe(manifest.getContainerNotes())+"',freight='"+
		manifest.getFreight()+"',trucking='"+manifest.getTrucking()+"',misc='"+
		manifest.getMisc()+"' WHERE job_num='"+jobNum+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public List getContainersWithContent(String jobNum)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		List containers=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from container where "+
			"container.job_num='"+jobNum+"' ");
			while (rs.next())
			{
				String id=rs.getString("id");
				String supplier = rs.getString("supplier");
				List contents=getContainerContents(jobNum,id);
				Container container=new Container(id,supplier);
				for (int count=0;count<contents.size();count++)
				{
					container.addContent((ContainerContent)contents.get(count));
				}
				containers.add(container);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		/*Container[] containerArr=new Container[containers.size()];
		for (int count=0;count>containerArr.length;count++)
		{
			containerArr[count]=(Container)containers.get(count);
		}
		return containerArr;*/
		return containers;
	}

    public void addContainer(String jobNum, Container container)
	throws DataAccessException {
		updateStatement("INSERT INTO container (job_num,id,supplier) VALUES ('"+
			sqlSafe(jobNum)+"','"+container.getId()+"','"+sqlSafe(container.getSupplier())+"')");

    }

    public void updateContainer(String jobNum, Container container)
	throws DataAccessException {
		updateStatement("UPDATE container set supplier='"+sqlSafe(container.getSupplier())+
			"' WHERE job_num='"+jobNum+"' AND id='"+container.getId()+"'");

    }
	
    public void deleteManifest(String jobNum)
	throws DataAccessException {
        deleteContainers(jobNum);
		updateStatement("DELETE FROM manifest WHERE job_num='"+sqlSafe(jobNum)+"'");
    }

	public void deleteContainers(String jobNum)
	throws DataAccessException {
		deleteContainerContents(jobNum);
		updateStatement("DELETE FROM  container WHERE job_num='"+sqlSafe(jobNum)+"'");
	}

    public void deleteContainer(String jobNum, String id)
		throws DataAccessException {
		deleteContainerContents(jobNum,id);
		updateStatement("DELETE FROM  container WHERE job_num='"+sqlSafe(jobNum)+
		"' and id='"+id+"'");
    }

	public void deleteContainerContents(String jobNum)
		throws DataAccessException {
		updateStatement("DELETE FROM  container_contents WHERE job_num='"+sqlSafe(jobNum)+"'");
    }

	public void deleteContainerContents(String jobNum, String id)
		throws DataAccessException {
		updateStatement("DELETE FROM  container_contents WHERE job_num='"+sqlSafe(jobNum)+
		"' and id='"+id+"'");
    }

    public void deleteContainerContent(String jobNum,String id,String description)
	throws DataAccessException {
		updateStatement("DELETE FROM  container_contents WHERE job_num='"+sqlSafe(jobNum)+
		"' and id='"+id+"' and description='"+description+"'");
    }

    public List getContainerContents(String jobNum,String id)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		List containerContents=new ArrayList();
		try
		{
			stat=connection.createStatement();
			String queryString="select * from container_contents where "+
			"job_num='"+sqlSafe(jobNum)+"' and id='"+id+"'";
			System.out.println(queryString);
			rs=stat.executeQuery(queryString);
			while (rs.next())
			{
				containerContents.add(new ContainerContent(rs.getString("description"),
				rs.getFloat("weight"),rs.getString("fob"),rs.getFloat("fobCNumber"),
				rs.getFloat("fobAmount"),rs.getFloat("cost"),rs.getString("remarks")));
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		/*ContainerContent[] containerContentArr=new ContainerContent[containerContents.size()];
		for (int count=0;count>containerContentArr.length;count++)
		{
			containerContentArr[count]=(ContainerContent)containerContents.get(count);
		}
		return containerContentArr;*/
		return containerContents;
    }
	
	public Container getContainer(String jobNum,String id)
	throws DataAccessException{
		Statement stat=null;
		ResultSet rs=null;
		Container container=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from container where "+
			"job_num='"+sqlSafe(jobNum)+"' and id='"+id+"'");
			if (rs.next())
			{
				container=new Container(id,rs.getString("supplier"));
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return container;
	}
	
    public ContainerContent getContainerContent(String jobNum,String id,String description)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		ContainerContent content=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from container_contents where "+
			"job_num='"+sqlSafe(jobNum)+"' and id='"+id+"' and description='"+description+"'");
			if (rs.next())
			{
				content=new ContainerContent(rs.getString("description"),
				rs.getFloat("weight"),rs.getString("fob"), rs.getFloat("fobCNumber"),
				rs.getFloat("fobAmount"),rs.getFloat("cost"),rs.getString("remarks"));
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return content;
    }
    public void addContainerContent(String jobNum,String id,ContainerContent content)
	throws DataAccessException {
		updateStatement("INSERT INTO container_contents  (job_num, id, description, "+
		"fob, fobCNumber, fobAmount, weight, cost, remarks) VALUES ('"+sqlSafe(jobNum)+"', '"+id+
		"', '"+sqlSafe(content.getDescription())+"', '"+content.getFob()+
		"', "+content.getFobCNumber()+", "+content.getFobAmount()+
		", "+content.getWeight()+", "+content.getCost()+", '"+sqlSafe(content.getRemarks())+"')");
    }


	 public static void main(String[] args) {
		try
		{
		    Properties props=new Properties();
		    props.load(new java.io.FileInputStream("C:\\perforce\\Sandbox\\gchen-dt\\pioneer_metal\\config\\db.prop"));
			ManifestDAO dao=new JdbcManifestDAO(props);
			/*
			Container[] containers=dao.getContainersWithContent("firstjob");
			System.out.println("Returned "+containers.length+" rows");
		    */
			/*
			ContainerContent content=new ContainerContent("description",43,"fob",34,52,"remarks");
			dao.addContainerContent("firstjob","2",content);
			*/
			dao.deleteContainerContent("firstjob","1","bla");
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
        //JdbcManifestDAO jdbcManifestDAO1 = new JdbcManifestDAO();
    }

}